const mysql = require('mysql2/promise');
const config = require('./config.json');

const pool = mysql.createPool(config.db);

module.exports = {
	// 查询单个用户
	async getUser(username) {
		const conn = await pool.getConnection();
		try {
			const [rows] = await conn.query('SELECT * FROM userinfo WHERE username = ?', [username]);
			return rows[0];
		} finally {
			conn.release();
		}
	},

	// 创建用户
	async createUser(username, password, role, id) {
		const conn = await pool.getConnection();
		try {
			await conn.query('INSERT INTO userinfo (username, password, role, id) VALUES (?, ?, ?, ?)', [
				username, password, role, id
			]);
		} finally {
			conn.release();
		}
	},

	// 保存数据到数据库
	async saveDataToDatabase(reportedData) {
		const conn = await pool.getConnection();
		try {
			// 裁剪小数点后四位并转换事件时间为日期
			const dataToInsert = {
				led: reportedData.properties.led,
				sig: reportedData.properties.sig,
				hum: parseFloat(reportedData.properties.hum.toFixed(4)),
				tem: parseFloat(reportedData.properties.tem.toFixed(4)),
				gas: parseFloat(reportedData.properties.gas.toFixed(4)),
				ap: parseFloat(reportedData.properties.ap.toFixed(4)),
				ur: parseFloat(reportedData.properties.ur.toFixed(4)),
			};

			// 插入数据到数据库
			const query =
				'INSERT INTO data (LED, SIG, HUM, TEM, GAS, AP, UR) VALUES (?, ?, ?, ?, ?, ?, ?)';
			await conn.query(query, [
				dataToInsert.led,
				dataToInsert.sig,
				dataToInsert.hum,
				dataToInsert.tem,
				dataToInsert.gas,
				dataToInsert.ap,
				dataToInsert.ur,
				dataToInsert.date
			]);
		} finally {
			conn.release();
		}
	}
};